/*==============================================================*/
/* Table: mb_MetabaseObjectTypes                                */
/*==============================================================*/
create table mb_MetabaseObjectTypes (
   Type_ID              int                  not null,
   Type_Name            nvarchar(200)        not null,
   Is_Container         int                  not null,
   Description          nvarchar(Max)        null,
   constraint PK_MB_METABASEOBJECTTYPES primary key (Type_ID)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   'Типизация объектов Метаданных',
   'user', @CurrentUser, 'table', 'mb_MetabaseObjectTypes'
go

begin
    declare @count_rec int
    declare @type_id int
    --------------
    set @type_id = 0
    select @count_rec = COUNT(*)
    from mb_MetabaseObjectTypes
    where TYPE_ID = @type_id
    ---
    if @count_rec = 0 begin
		insert into mb_MetabaseObjectTypes
		(Type_ID, Type_Name, Is_Container)
		values
		(@type_id, 'Metabase', 1)
    end
    --------------
    set @type_id = 1
    select @count_rec = COUNT(*)
    from mb_MetabaseObjectTypes
    where TYPE_ID = @type_id
    ---
    if @count_rec = 0 begin
		insert into mb_MetabaseObjectTypes
		(Type_ID, Type_Name, Is_Container)
		values
		(@type_id, 'Folder', 1)
    end
end;


/*==============================================================*/
/* Index: i_TypeName                                            */
/*==============================================================*/
create unique index i_TypeName on mb_MetabaseObjectTypes (
Type_Name ASC
)
go

/*==============================================================*/
/* Table: mb_MetabaseStructure                                  */
/*==============================================================*/
create table mb_MetabaseStructure (
   ID                   int                  identity,
   External_ID          nvarchar(100)        null,
   Parent_ID            int                  null,
   Type_ID              int                  not null,
   Object_Name          nvarchar(500)        not null,
   Description          nvarchar(Max)        null,
   Modify_Date          datetime             not null,
   constraint PK_MB_METABASESTRUCTURE primary key (ID)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   'Структура Метаданных',
   'user', @CurrentUser, 'table', 'mb_MetabaseStructure'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   'Внешний идентификатор объекта (для процедур экспорта / импорта)',
   'user', @CurrentUser, 'table', 'mb_MetabaseStructure', 'column', 'External_ID'
go

begin
    declare @count_rec int
    declare @type_id int = 0
    declare @metabase_name nvarchar(500) = {0} -- Метаподстановка для запуска в C#
    --------------
    select @count_rec = COUNT(*)
    from mb_MetabaseStructure
    where TYPE_ID = @type_id
    ---
    if @count_rec = 0 begin
		insert into mb_MetabaseStructure
		(Type_ID, Object_Name, Modify_Date)
		values
		(@type_id, @metabase_name, SYSDATETIME())
    end
end;

/*==============================================================*/
/* Index: i_parent_id                                           */
/*==============================================================*/
create index i_parent_id on mb_MetabaseStructure (
Parent_ID ASC
)
go

alter table mb_MetabaseStructure
   add constraint FK_MB_METAB_REF_MB_METAB_1 foreign key (Parent_ID)
      references mb_MetabaseStructure (ID)
go

alter table mb_MetabaseStructure
   add constraint FK_MB_METAB_REFERENCE_MB_METAB foreign key (Type_ID)
      references mb_MetabaseObjectTypes (Type_ID)
go


create procedure mb_CreateObject 
@External_ID nvarchar(100), 
@Parent_ID int, 
@Type_ID int, 
@Object_Name nvarchar(500), 
@Description nvarchar(4000),
@Id int output
as
begin
    insert into mb_MetabaseStructure 
    (External_ID, Parent_ID, Type_ID, Object_Name, Description, Modify_Date) 
    values 
    (@External_ID, @Parent_ID, @Type_ID, @Object_Name, @Description, SYSDATETIME())
    ---
    set @Id = SCOPE_IDENTITY()
end
go


create procedure mb_DeleteObject 
@Object_ID integer
as
begin
	SET NOCOUNT ON;
	delete from mb_MetabaseStructure where ID = @Object_ID
	SET NOCOUNT OFF;
end
go


create procedure mb_UpdateObject 
@External_ID nvarchar(100), 
@Parent_ID int, 
@Type_ID int, 
@Object_Name nvarchar(500), 
@Description nvarchar(4000),
@Id int
as
begin
    update mb_MetabaseStructure 
    set External_ID = @External_ID,
        Parent_ID = @Parent_ID,
        Type_ID = @Type_ID,
        Object_Name = @Object_Name,
        Description = @Description,
        Modify_Date = SYSDATETIME()
    where Id = @Id
end
go
